package com.dmgis.hndz.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dmgis.hndz.pojo.Zhaa03b;
import com.dmgis.hndz.pojo.Zhaa11A;
import org.apache.ibatis.annotations.*;

import java.util.List;


/**
 * 地质灾害调查基本信息表(Zhaa11a)表数据库访问层
 *
 * @author makejava
 * @since 2024-01-15 13:56:55
 */
@Mapper
public interface Zhaa03bMapper extends BaseMapper<Zhaa03b> {

    // @Select("select * from zhaa03b where zhaa03b020 = '${advcode}' and zhaa03b010 like '${advcode}\\_%' order by  zhaa03b010")
     @Select("select * from zhaa03b order by zhaa03b010")
    List<Zhaa03b> ztlayertree(@Param("advcode") String advcode);

    @Insert("INSERT INTO zhaa03b ( zhaa03b010, zhaa03b020, zhaa03b030, zhaa03b040, zhaa03b050, zhaa03b060, zhaa03b070, zhaa03b080, zhaa03b090, zhaa03b100, zhaa03b110, zhaa03b120, zhaa03b130 ) VALUES (#{zhaa03b010}, #{zhaa03b020}, #{zhaa03b030}, #{zhaa03b040}, #{zhaa03b050}, #{zhaa03b060}, #{zhaa03b070}, #{zhaa03b080}, #{zhaa03b090}, #{zhaa03b100}, #{zhaa03b110}, #{zhaa03b120}, #{zhaa03b130} )")
    int addLayer (Zhaa03b zhaa03b);

    @Update("UPDATE zhaa03b SET zhaa03b030 = #{zhaa03b030}, zhaa03b040 = #{zhaa03b040}, zhaa03b050 = #{zhaa03b050}, zhaa03b060 = #{zhaa03b060}, zhaa03b070 = #{zhaa03b070}, zhaa03b080 = #{zhaa03b080}, zhaa03b090 = #{zhaa03b090}, zhaa03b100 = #{zhaa03b100}, zhaa03b110 = #{zhaa03b110}, zhaa03b120 = #{zhaa03b120}, zhaa03b130 = #{zhaa03b130} WHERE zhaa03b010 = #{zhaa03b010}")
    int updateLayer(Zhaa03b zhaa03b);

    @Delete("WITH RECURSIVE subtree AS (\n" +
            "\tSELECT \n" +
            "\t\tzhaa03b010,\n" +
            "\t\tzhaa03b020\n" +
            "\tFROM\n" +
            "\t\tzhaa03b\n" +
            "\tWHERE\n" +
            "\t\tzhaa03b010 = #{id}\n" +
            "\tUNION ALL\n" +
            "\tSELECT\n" +
            "\t\ttn.zhaa03b010,\n" +
            "\t\ttn.zhaa03b020 \n" +
            "\tFROM\n" +
            "\t\tzhaa03b tn\n" +
            "\t\tINNER JOIN subtree st ON tn.zhaa03b020 = st.zhaa03b010\n" +
            "\t) \n" +
            "\tDELETE FROM zhaa03b \n" +
            "\tWHERE zhaa03b010 IN ( SELECT zhaa03b010 FROM subtree )")
    int deleteLayer(String id);
}
